package com.bds.wm.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bds.wm.model.view.*;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import org.apache.log4j.Logger;
import org.nutz.dao.Cnd;
import org.nutz.dao.Condition;
import org.nutz.dao.Sqls;
import org.nutz.dao.impl.NutDao;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created by guoyu on 2018/5/21.
 */
@Service
public class ViewService {
    private static Logger LOGGER = Logger.getLogger(ViewService.class);
    @Resource
    private NutDao dao;

    public List<CityView> getCityTier() {
        List<CityView> tmp = dao.query(CityView.class, null);
        CityView[] cityViews = new CityView[tmp.size()];
        List<CityView> result = new LinkedList<>();
        for (CityView c : tmp) {
            String s = c.getCityTierName().toLowerCase();
            if (s.equals("all")) {
                c.setCityTierRealName("所有城市");
                cityViews[0] = c;
            } else if (s.equals("tier 1")) {
                c.setCityTierRealName(c.getCityTierName());
                cityViews[1] = c;
            } else if (s.equals("tier 2")) {
                c.setCityTierRealName(c.getCityTierName());
                cityViews[2] = c;
            } else if (s.equals("tier 3")) {
                c.setCityTierRealName(c.getCityTierName());
                cityViews[3] = c;
            } else if (s.equals("tier 4")) {
                c.setCityTierRealName(c.getCityTierName());
                cityViews[4] = c;
            }
        }
        for (CityView c : cityViews) {
            result.add(c);
        }
        return result;
    }

    public List<BrandName> getBrandName() {
        return dao.query(BrandName.class, null);
    }

    public List<SubCategoryName> getSubCategoryName() {
        return dao.query(SubCategoryName.class, null);
    }

    public List<String> getCatNames() {
        Sql sql = Sqls.create("select count(a.SubCategoryName),SubCategoryName from dbo.v_brand_name a group by a.SubCategoryName HAVING a.SubCategoryName is not null");
        sql.setCallback(new SqlCallback() {
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                List<String> list = new LinkedList<String>();
                while (rs.next())
                    list.add(rs.getString("SubCategoryName"));
                return list;
            }
        });
        dao.execute(sql);
        return sql.getList(String.class);
    }

    public List<CityNameView> getCityName() {
        List<CityNameView> list = dao.query(CityNameView.class, null);
        Collections.sort(list, new Comparator<CityNameView>() {
            @Override
            public int compare(CityNameView o1, CityNameView o2) {
                return o1.getCityID().compareTo(o2.getCityID());
            }
        });
        return list;
    }

    public JSONObject getCityTierName() {
        JSONObject jsonObject = new JSONObject(true);
        List<CityTierName> list = dao.query(CityTierName.class,
                Cnd.where("CityID", "<>", "").orderBy("CityID", "ASC"));
        Sql sql = Sqls.create("SELECT DISTINCT CityTier FROM v_city_tier_name");
        sql.setCallback(new SqlCallback() {
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                List<String> list = new LinkedList<String>();
                while (rs.next())
                    list.add(rs.getString("CityTier"));
                return list;
            }
        });
        dao.execute(sql);
        List<String> cityTierListTmp = sql.getList(String.class);
        CityView[] cityTierArray = new CityView[cityTierListTmp.size()];
        List<CityView> cityTierList = new LinkedList<>();

        for (String s : cityTierListTmp) {
            CityView c = new CityView();
            c.setCityTierName(s);
            if (s.toLowerCase().equals("all")) {
                c.setCityTierRealName("所有城市");
                cityTierArray[0] = c;
            } else if (s.toLowerCase().equals("tier 1")) {
                c.setCityTierRealName(c.getCityTierName());
                cityTierArray[1] = c;
            } else if (s.toLowerCase().equals("tier 2")) {
                c.setCityTierRealName(c.getCityTierName());
                cityTierArray[2] = c;
            } else if (s.toLowerCase().equals("tier 3")) {
                c.setCityTierRealName(c.getCityTierName());
                cityTierArray[3] = c;
            } else if (s.toLowerCase().equals("tier 4")) {
                c.setCityTierRealName(c.getCityTierName());
                cityTierArray[4] = c;
            }
        }
        for (CityView c : cityTierArray) {
            cityTierList.add(c);
        }

        jsonObject.put("city_tier", cityTierList);
        jsonObject.put("city_tier_name", list);

        return jsonObject;
    }

    public YearMonth getLatestMonth() {
        return dao.query(YearMonth.class, null).get(0);
    }

    public JSONObject getTopSubcateBrandName() {
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("sub_cate", new JSONArray());
        List<TopSubCateBrandName> topSubCateBrandNameList = dao.query(TopSubCateBrandName.class,
                Cnd.where("BrandID", "<>", "").orderBy("BrandID", "ASC"));
        Sql sql = Sqls.create("SELECT DISTINCT SubCategoryName FROM v_top_subCategory_brand_name");
        sql.setCallback(new SqlCallback() {
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                List<String> list = new LinkedList<String>();
                while (rs.next())
                    list.add(rs.getString("SubCategoryName"));
                return list;
            }
        });
        dao.execute(sql);
        List<String> subCateList = sql.getList(String.class);
        jsonObject.getJSONArray("sub_cate").addAll(subCateList);
        for (String s : subCateList) {
            jsonObject.put(s, new JSONArray());
            for (TopSubCateBrandName t : topSubCateBrandNameList) {
                if (s.equals(t.getSubCategoryName())) {
                    jsonObject.getJSONArray(s).add(t.getBrandName());
                }
            }
        }

        return jsonObject;
    }
    public JSONObject getRegionCityBrandName() {
        List<RegionCityBrandName> regionCityBrandNames = dao.query(RegionCityBrandName.class,  Cnd.cri().asc("RegionID").asc("CityID"));
        JSONObject jsonObject = new JSONObject(true);
        LinkedHashMap<String,Set<String>> cities=Maps.newLinkedHashMap();//province:cities
        LinkedHashMap<String,Set<String>> brands=Maps.newLinkedHashMap();//city:brands
        regionCityBrandNames.forEach(region->{
            String region1 = region.getRegion();
            String city = region.getCity();
            String brandName = region.getBrandName();
            if(cities.containsKey(region1)){
                Set<String> cits = cities.get(region1);
                cits.add(city);
                cities.put(region1,cits);
            }else{
                cities.put(region1,Sets.newHashSet(city));
            }
            if(brands.containsKey(city)){
                Set<String> cits = brands.get(city);
                cits.add(brandName);
                brands.put(city,cits);
            }else{
                brands.put(city,Sets.newHashSet(brandName));
            }

        });
        jsonObject.put("cities",cities);
        jsonObject.put("brands",brands);
        return jsonObject;
    }


}
